package com.csust.advanced;

import com.csust.advanced.pojo.Employee;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCAdvanced {
    @Test
    public void test() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode = true&characterEncoding=utf8&useSSL=false","root","123456");

        PreparedStatement ps = conn.prepareStatement("SELECT `emp_id`,`emp_name`,`emp_salary`,`emp_age` FROM `t_emp` WHERE `emp_id` = ?  ");

        ps.setInt(1, 1);

        ResultSet rs = ps.executeQuery();

        Employee emp = null;

        while (rs.next()) {
            emp = new Employee();
            int empId = rs.getInt("emp_id");
            String empName = rs.getString("emp_name");
            Double salary = rs.getDouble("emp_salary");
            int age = rs.getInt("emp_age");
            emp.setEmpId(empId);
            emp.setEmpName(empName);
            emp.setEmpSalary(salary);
            emp.setEmpAge(age);

        }
        System.out.println(emp);


        rs.close();
        ps.close();
        conn.close();
    }
    @Test
    public void test1() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");

        PreparedStatement ps = conn.prepareStatement("select * from `t_emp`");


        Employee emp = null;
        List<Employee> list = new ArrayList<>();

        ResultSet rs = ps.executeQuery();

        while (rs.next()){
            emp = new Employee();
            emp.setEmpId(rs.getInt("emp_id"));
            emp.setEmpName(rs.getString("emp_name"));
            emp.setEmpSalary(rs.getDouble("emp_salary"));
            emp.setEmpAge(rs.getInt("emp_age"));
            list.add(emp);
        }
//        System.out.println(list);
        for (Employee empl : list) {
            System.out.println(empl);
        }


        rs.close();
        ps.close();
        conn.close();










    }
    @Test
    public void test2() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");


        String sql = "insert `t_emp` (emp_Name,emp_salary,emp_age) values (?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

        Employee emp = new Employee(0,"sail",25000.00,20);


        ps.setString(1,emp.getEmpName());
        ps.setDouble(2,emp.getEmpSalary());
        ps.setInt(3,emp.getEmpAge());
        int result = ps.executeUpdate();
        ResultSet rs = null;
        if(result > 0){
            System.out.println("成功！");
            rs = ps.getGeneratedKeys();
            while (rs.next()){
                int id = rs.getInt(1);
                emp.setEmpId(id);
            }
            System.out.println(emp);
        }
        else {
            System.out.println("失败！");
        }
        if(rs!=null){
            rs.close();
        }
        ps.close();
        conn.close();
    }
    @Test
    public void test3() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&useSSL=false","root","123456");

        String sql = "insert into `t_emp` (`emp_name`,`emp_salary`,`emp_age`) values (?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            ps.setString(1,"sail"+i);
            ps.setDouble(2,i+100.00);
            ps.setInt(3,i);

            ps.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("消耗时间:"+(start-end));
        ps.close();
        conn.close();

    }


    @Test
    public void testBatch4() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?rewriteBatchedStatements=true&useUnicode=true&useSSL=false","root","123456");

        String sql = "insert into `t_emp` (`emp_name`,`emp_salary`,`emp_age`) values (?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        long start = System.currentTimeMillis();
            /*
        注意：1、必须在连接数据库的URL后面追加?rewriteBatchedStatements=true，允许批量操作
            2、新增SQL必须用values。且语句最后不要追加;结束
            3、调用addBatch()方法，将SQL语句进行批量添加操作
            4、统一执行批量操作，调用executeBatch()
            */



        for (int i = 0; i < 10000; i++) {
            ps.setString(1,"sail"+i);
            ps.setDouble(2,i+100.00);
            ps.setInt(3,i);
            ps.addBatch();
        }
        ps.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("消耗时间:"+(start-end));
        ps.close();
        conn.close();



    }

}
